CREATE EXTERNAL TABLE IF NOT EXISTS spmi_ods.spmi_capacity_month_bill(
id                                                 bigint                         COMMENT '主键ID',
bill_no                                            String                         COMMENT '账单流水号',
bill_generation_time                               timestamp                      COMMENT '账单产生时间',
bill_month                                         String                         COMMENT '账单月份',
bill_cycle                                         String                         COMMENT '账单(操作)周期',
carrier_id                                         bigint                         COMMENT '承运商ID',
carrier_name                                       String                         COMMENT '承运商名称',
sum_task_no                                        bigint                         COMMENT '任务号总数',
sum_handle_freight                                 Double                         COMMENT '单程含税运价',
sum_amount                                         Double                         COMMENT '罚款总金额',
payment_amount                                     Double                         COMMENT '实付金额',
invoice_amount                                     Double                         COMMENT '开票金额',
sum_time_deduction                                 Double                         COMMENT '时效扣款',
sum_standard_deduction                             Double                         COMMENT '标准化扣款',
sum_quality_deduction                              Double                         COMMENT '质控扣款',
gps_cost                                           Double                         COMMENT 'GPS费用',
top_shuttle_cost                                   Double                         COMMENT '顶班车费用',
overtime_cost                                      Double                         COMMENT '应承担应急加班费',
purchase_deduction                                 Double                         COMMENT '采购扣款',
factoring                                          Double                         COMMENT '保理',
other_deduction                                    Double                         COMMENT '其他扣款',
move_spread                                        Double                         COMMENT '挪挂���价',
config_deduction                                   Double                         COMMENT '配置不符扣款',
car_subsidy                                        Double                         COMMENT '压车补贴',
is_confirm                                         smallint                       COMMENT '承运商确认标识1是2否',
is_return                                          smallint                       COMMENT '发票回传标识1是2否',
invoice_url                                        String                         COMMENT '发票附件路径',
is_verifier                                        smallint                       COMMENT '审核标识1是2否',
verifier_id                                        bigint                         COMMENT '审核人ID',
verifier_name                                      String                         COMMENT '审核人名称',
verifier_time                                      timestamp                      COMMENT '审核时间',
remarks                                            String                         COMMENT '备注',
create_by                                          bigint                         COMMENT '创建人ID',
create_by_name                                     String                         COMMENT '创建人名称',
create_time                                        timestamp                      COMMENT '创建时间',
update_by                                          bigint                         COMMENT '最后更新人ID',
update_by_name                                     String                         COMMENT '最后修改人名称',
update_time                                        timestamp                      COMMENT '更新时间',
version                                            bigint                         COMMENT '版本号',
sort                                               smallint                       COMMENT '排序',
trace_id                                           String                         COMMENT 'TRACEID',
sum_reconciliation_amount                          Double                         COMMENT '调账总金额',
sum_oil_price                                      Double                         COMMENT '油价联动金额',
un_auditor_name                                    String                         COMMENT '反审核人',
un_auditor_id                                      bigint                         COMMENT '反审核人ID',
un_auditor_time                                    timestamp                      COMMENT '反审核时间'
)
COMMENT '承运商月结账单表'
PARTITIONED BY (dt STRING COMMENT '更新/创建日期 (yyyy-MM-dd)')
STORED AS PARQUET
LOCATION '/dw/hive/spmi_ods.db/external/spmi_capacity_month_bill'
TBLPROPERTIES (
  'parquet.column.index.access'='true'
);
